package ufs.dcomp.mgemeos.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import ufs.dcomp.mgemeos.negocio.Exposicao;
import ufs.dcomp.mgemeos.perfis.Expositor;

/**
 *
 * @author Luiz Marcus
 * @modificacao 06/02/11
 */
public class ExpositorDAO implements interface_dao.IExpositor {

    private static String inserir = "insert into expositor (nome, cpf, email, login, senha, idEvento) values (?,?,?,?,?,?)";
    private static String atualizar = "update expositor set nome =?, cpf=?, email=?, login=?, senha=?, idEvento=? where idExpositor=?";
    private static String obter = "select * from expositor where idExpositor = ? ";
    private static String obter_por_login = "select * from expositor where login = ? ";
    private static String obter_todos = "select * from expositor ORDER BY nome";
    private static String delete = "delete from expositor where idExpositor = ? ";

    @Override
    public boolean atualizarExpositor(Expositor expositor) {
        Conexao conexao = new Conexao();
        Connection con = conexao.getConnection();
        PreparedStatement pst;
        try {
            pst = con.prepareStatement(atualizar);
            pst.setString(1, expositor.getNome());
            pst.setString(2, expositor.getCpf());
            pst.setString(3, expositor.getEmail());
            pst.setString(4, expositor.getLogin());
            pst.setString(5, expositor.getSenha());
            pst.setInt(6, expositor.getIdEvento());
            pst.setInt(7, expositor.getIdExpositor());
            pst.executeUpdate();
            pst.close();
            con.close();
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    public Expositor obterExpositor(int id) {
        Expositor expositor = null;
        Conexao conexao = new Conexao();
        Connection con = conexao.getConnection();
        PreparedStatement pst;
        ArrayList<Exposicao> exposicoes = new ArrayList<Exposicao>();
        try {
            pst = con.prepareStatement(obter);
            pst.setInt(1, id);
            ResultSet rs = pst.executeQuery();
            if (rs.next()) {
                exposicoes = new ExposicaoDAO().obter_Expositor_Exposicoes(rs.getInt(1));
                expositor = new Expositor();
                expositor.setIdExpositor(rs.getInt(1));
                expositor.setNome(rs.getString(2));
                expositor.setCpf(rs.getString(3));
                expositor.setEmail(rs.getString(4));
                expositor.setLogin(rs.getString(5));
                expositor.setSenha(rs.getString(6));
                expositor.setIdEvento(rs.getInt(7));
                expositor.setExposicoes(exposicoes);
            }
            rs.close();
            pst.close();
            con.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return expositor;
    }

    @Override
    public Expositor obterExpositor_por_Login(String login) {
        Expositor expositor = null;
        Conexao conexao = new Conexao();
        Connection con = conexao.getConnection();
        PreparedStatement pst;
        try {
            pst = con.prepareStatement(obter_por_login);
            pst.setString(1, login);
            ResultSet rs = pst.executeQuery();
            if (rs.next()) {

                expositor = new Expositor();
                expositor.setIdExpositor(rs.getInt(1));
                expositor.setNome(rs.getString(2));
                expositor.setCpf(rs.getString(3));
                expositor.setEmail(rs.getString(4));
                expositor.setLogin(rs.getString(5));
                expositor.setSenha(rs.getString(6));
                expositor.setIdEvento(rs.getInt(7));
                expositor.setExposicoes(null);
            }
            rs.close();
            pst.close();
            con.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return expositor;
    }

    @Override
    public boolean inserirExpositor(Expositor expositor) {
        Conexao conexao = new Conexao();
        Connection con = conexao.getConnection();
        PreparedStatement pst;

        Expositor expositor2 = obterExpositor(expositor.getIdExpositor());

        if (expositor2 == null) {
            try {
                pst = con.prepareStatement(inserir);
                pst.setString(1, expositor.getNome());
                pst.setString(2, expositor.getCpf());
                pst.setString(3, expositor.getEmail());
                pst.setString(4, expositor.getLogin());
                pst.setString(5, expositor.getSenha());
                pst.setInt(6, expositor.getIdEvento());
                pst.execute();
                pst.close();
                con.close();
                if (expositor.getExposicoes() != null) {
                    for (Exposicao exposicao : expositor.getExposicoes()) {
                        //ExposicaoDAO.inserirExposicao(exposicao);
                        ExposicaoDAO exp = new ExposicaoDAO();
                        exp.inserirExposicao(exposicao);
                    }
                }
                return true;
            } catch (SQLException e) {
                e.printStackTrace();
                return false;
            }
        } else {
            return false;
        }
    }

    @Override
    public boolean deletarExpositor(int id) {
        Conexao conexao = new Conexao();
        Connection con = conexao.getConnection();
        PreparedStatement pst;

        Expositor expositor2 = obterExpositor(id);

        if (expositor2 != null) {
            try {
                pst = con.prepareStatement(delete);
                pst.setInt(1, id);
                pst.execute();
                pst.close();
                con.close();
                return true;
            } catch (SQLException e) {
                e.printStackTrace();
                return false;
            }
        } else {
            return false;
        }
    }

    @Override
    public ArrayList<Expositor> obterExpositor_todos() {
        Expositor expositor = null;
        ArrayList<Expositor> lista_expositors = new ArrayList<Expositor>();
        Conexao conexao = new Conexao();
        Connection con = conexao.getConnection();
        PreparedStatement pst;
        ArrayList<Exposicao> exposicoes = new ArrayList<Exposicao>();
        try {
            pst = con.prepareStatement(obter_todos);
            ResultSet rs = pst.executeQuery();
            while (rs.next()) {
                
                exposicoes = new ExposicaoDAO().obter_Expositor_Exposicoes(rs.getInt(1));
                expositor = new Expositor();
                expositor.setIdExpositor(rs.getInt(1));
                expositor.setNome(rs.getString(2));
                expositor.setCpf(rs.getString(3));
                expositor.setEmail(rs.getString(4));
                expositor.setLogin(rs.getString(5));
                expositor.setSenha(rs.getString(6));
                expositor.setIdEvento(rs.getInt(7));
                expositor.setExposicoes(exposicoes);

                lista_expositors.add(expositor);
            }
            rs.close();
            pst.close();
            con.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return lista_expositors;
    }

   
}
